/**   
 * Copyright © 2019 eSunny Info. Tech Ltd. All rights reserved.
 * 
 * @Description:TODO(功能)
 * @className ProductsDaoImpl.java
 * @Package: com.erp.dao 
 * @author: yj   
 * @date: 2019年1月17日 上午9:52:14 
 */
package com.erp.dao;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;

import com.erp.po.Products;
import com.erp.util.Page;

/**
 * @className ProductsDaoImpl.java
 * @author yj
 * @Description:TODO(功能)
 * @Date 2019年1月17日
 */
public class ProductsDaoImpl extends BaseDao implements ProductsDao {

	// ------------------------------ Products
	// -----------------------------------------------
	/*
	 * (查询 ( 查询 Products <商品> 全部 )(分页))
	 * 
	 */
	@Override
	public ResultSet selectProductsByPage(Connection conn, Page<Products> page) throws SQLException {
		// String sql = "select a.productID,a.product_name,a.income_price,a.sales_price,c.category_name,b.provider_name,a.income_time,a.quantity from products a,providers b,categorys c where a.providerID = b.providerID and a.categoryID = c.categoryID\r\n"
		// 		+ " ORDER BY a.productID  LIMIT ? , ? ";	// mysql 
		String sql = "select * " + 
				" from " + 
				" (select rownum rn, a.productID,a.product_name,a.income_price,a.sales_price,c.category_name,b.provider_name,a.income_time,a.quantity from products a,providers b,categorys c where a.providerID = b.providerID and a.categoryID = c.categoryID and rownum <= ? ORDER BY a.productID) info " + 
				" where info.rn > ?";
		// Object[] obj = { ((page.getCurrentPage() - 1) * page.getPageSize()), page.getPageSize() };
		Object[] obj = { (page.getCurrentPage() * page.getPageSize()), (page.getCurrentPage() - 1) * page.getPageSize() };
		return super.selectData(conn, sql, obj);
	}

	/*
	 * ( 查询 ( 查询 总记录数 ))
	 * 
	 */
	@Override
	public ResultSet selectProductsByTotal(Connection conn) throws SQLException {
		String sql = "select count(*) from products";
		return super.selectData(conn, sql, null);
	}

	/*
	 * (增加 ( 增加 商品 ))
	 * 
	 */
	@Override
	public int insertProducts(Connection conn, Products products) throws SQLException {
		String sql = "insert into products(productID,product_name,income_price,providerID,quantity,"
				+ " sales_price, categoryID ,income_time) values(products_seq.nextval,?,?,?,?,?,?,sysdate)";
		Object[] obj = { products.getProductName(), products.getIncomePrice(), products.getProviders().getProviderId(),
				products.getQuantity(), products.getSalesPrice(), products.getCategorys().getCategoryId() };
		return super.updateData(conn, sql, obj);
	}
	/* (查询 (是否有添加重名的商品))
	 * 
	 */
	@Override
	public ResultSet selectProductsByInsert(Connection conn , Products products) throws SQLException {
		String sql = "select * from products where product_name = ? ";
		return super.selectData(conn, sql, products.getProductName());
	}
	/* (查询 (商品类别id是否有存在))
	 * 
	 */
	@Override
	public ResultSet selectProductsByCateId(Connection conn, Products products) throws SQLException {
		String sql = "select count(*) from categorys where categoryID = ? ";
		return super.selectData(conn, sql, products.getCategorys().getCategoryId());
	}

	/* (查询 (供应商id是否有存在))
	 * 
	 */
	@Override
	public ResultSet selectProductsByProId(Connection conn, Products products) throws SQLException {
		String sql = "select count(*) from providers where providerID = ? ";
		return super.selectData(conn, sql, products.getProviders().getProviderId());
	}
	
	

	/*
	 * ( 修改 ( 修改商品属性: 商品名称 , 进价 , 售价 , 存货(数量) ))
	 * 
	 */
	@Override
	public int updateProductsById(Connection conn, Products products) throws SQLException {
		// 将date 类型的 转换成 String 类型
		SimpleDateFormat formatter = new SimpleDateFormat("yyyy-mm-dd");
		String sql = "update products set product_name = ? , income_price = ? , sales_price = ? ,"
				+ " quantity = ? , income_time = to_date(?,'yyyy-mm-dd') , providerID = ? , categoryID = ? where productID = ?";
		Object[] obj = { products.getProductName(), products.getIncomePrice(), products.getSalesPrice(),
				products.getQuantity(), formatter.format(products.getIncomeTime()), products.getProviders().getProviderId(),
				products.getCategorys().getCategoryId(), products.getProductId() };
		return super.updateData(conn, sql, obj);
	}

	/* ( 删除 ( 根据id 删除商品 ))
	 * 
	 */
	@Override
	public int deleteProductsById(Connection conn, int productId) throws SQLException {
		String sql = "delete from products where productID = ? ";
		return super.updateData(conn, sql, productId);
	}

	/* (删除 ( 删除 供应商 ) -- 外键校验)
	 * 
	 */
	@Override
	public ResultSet selectProvidersByFkCheck(Connection conn , int providerId) throws SQLException {
		String sql = "select count(*) from products p , providers pro "
				+ "where pro.providerID = ? and p.providerID = pro.providerID";
		return super.selectData(conn, sql, providerId);
	}
	
	/*
	 * (删除 ( 删除 类别 ) -- 外键校验 )
	 * 
	 */
	@Override
	public ResultSet selectCategorysByFkCheck(Connection conn, int categoryId) throws SQLException {
		String sql = "select count(*) from categorys c , products p where c.categoryID = ? and c.categoryID = p.categoryID";
		return super.selectData(conn, sql, categoryId);
	}

	/* (根据 id 查询product)
	 * 
	 */
	@Override
	public ResultSet selectProductById(Connection conn, int id) throws SQLException {
		String sql = "select * from Products where ProductID = ? ";
		return super.selectData(conn, sql, id);
	}

	/**
	 * 校验product重名
	 */
	public ResultSet selectProductByName(Connection conn, String productName) throws SQLException {
		String sql = "select * from Products where product_name = ? ";
		return super.selectData(conn, sql, productName);
	}

	/*
	 *  根据条件查询 ( 根据条件查询 Products <商品> 全部 )(分页)
	 */
	@Override
	public ResultSet selectProductsByCondition(Connection conn, Products p, Page<Products> page) throws SQLException {
		String sql = "select * from " + 
				" (select rownum rn, a.productID,a.product_name,a.income_price,a.sales_price,c.category_name,b.provider_name,to_char(a.income_time,'yyyy-MM-dd') income_time,a.quantity from products a,providers b,categorys c where a.providerID = b.providerID and a.categoryID = c.categoryID ";
		ArrayList list = new ArrayList();
		
		if(p.getProductName() != null && !"".equals(p.getProductName())) {
			sql = sql.concat(" and a.product_name like ? ");
			list.add("%"+p.getProductName()+"%");
		}
		if(p.getCategorys().getCategoryId() != 0) {
			sql = sql.concat(" and c.categoryID = ? ");
			list.add(p.getCategorys().getCategoryId());
		}
		if(p.getProviders().getProviderId() != 0) {
			sql = sql.concat(" and b.providerID = ? ");
			list.add(p.getProviders().getProviderId());
		}
		
		sql = sql.concat(" and rownum <= ? ORDER BY a.productID) info where info.rn > ? ");
		list.add((page.getCurrentPage() * page.getPageSize()));
		list.add((page.getCurrentPage() - 1) * page.getPageSize());
		
		return super.selectData(conn, sql, list.toArray());
	}

	/* 
	 * 根据条件查询 ( 根据条件查询 总记录数 )
	 */
	@Override
	public ResultSet selectConditionByTotal(Connection conn, Products p) throws SQLException {
		String sql = "select count(productid) from products where 1=1 ";
		
		ArrayList list = new ArrayList();
		
		if(p.getProductName() != null && !"".equals(p.getProductName())) {
			sql = sql.concat(" and product_name like ? ");
			list.add("%"+p.getProductName()+"%");
		}
		if(p.getCategorys().getCategoryId() != 0) {
			sql = sql.concat(" and categoryid = ? ");
			list.add(p.getCategorys().getCategoryId());
		}
		if(p.getProviders().getProviderId() != 0) {
			sql = sql.concat(" and providerid = ? ");
			list.add(p.getProviders().getProviderId());
		}
		
		// System.err.println("信息 :"+sql+"  ---ProductsDaoImpl");
		
		return super.selectData(conn, sql, list.toArray());
	}


}
